package com.lrs.java.jdbc.jdbc_teacher;
import java.io.File;
import java.io.FileReader;
import java.io.Writer;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;

import oracle.sql.CLOB;

/**
 *  首先来说明 BLob 型数据和 CLob 型数据的差别：
 *  在 Oracle 中 BLob 型的字段主要用来记录二进制数据，如：图片、媒体、音乐以及压缩文件等等等等……。
 *  而 Clob 主要用来记录大字段的文本信息。
 *  最大长度是4GB
 *  因为在默认情况下 Clob 对象是 Null 型的，对一个 Null 型对象在进行操作时会抛出“空指针异常”
 *  因此，我们需要在该字段写入一个空 Clob 对象。
 *  
 *  create table myclob(
 *     clobid number(6),
 *     mydata clob
 * )
 * insert into myclob (clobid,mydata) values(2222,empty_clob())
 * 不要忘记commit!
 * select * from myclob
 * 
 * 10g,可以直接rs.getString(myclob)
 */
public class TestClob {
	static Connection conn = null;
	static String url = "jdbc:oracle:thin:@192.168.0.66:1521:BJSXT";

	public static void main(String[] args) throws Exception {
		Class.forName("oracle.jdbc.driver.OracleDriver");
		conn = DriverManager.getConnection(url, "scott", "tiger");
		
		setClob(null);

		System.out.println(showClob());
		
		conn.close();
		System.out.println("ok...");
	}

	public void clearClob() throws Exception {
		Statement ps = conn.createStatement();
		String updateSQL = "update myclob set mydata=empty_clob() where clobid='1111'  and mydata is null";
		ps.executeUpdate(updateSQL);
	}

	public static void setClob(String values) throws Exception {

		Statement ps = null;
		ResultSet rs = null;
		ps = conn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,
				ResultSet.CONCUR_UPDATABLE);
		//在锁定单行记录的情况下，只能采用连接（conn）提交方式而不是记录集（ps）提交方式。
		conn.setAutoCommit(false);
		// FOR UPDATE 子句，相应行上会加上互斥锁,直到整个事务被提交为止。
		rs = ps
				.executeQuery("select clobid, mydata from myclob where clobid='2222' for update");
		java.sql.Clob clob = null;
		if (rs.next()) {
			clob = rs.getClob("mydata");
			CLOB myclob = (CLOB) clob;
			Writer outStream = myclob.getCharacterOutputStream();
			File f = new File("c:\\xxmj.txt");
			FileReader fr = new java.io.FileReader(f);
			char[] c = new char [(int)f.length()];
			fr.read(c);
			//char[] c = "aaa".toCharArray();
			//			char[] c = values.toCharArray();
			outStream.write(c, 0, c.length);
			outStream.flush();
			outStream.close();
		}
		conn.commit();
		rs.close();
		ps.close();
	}

	public static String showClob() throws Exception {

		String str = null;
		Statement ps = conn.createStatement(ResultSet.TYPE_FORWARD_ONLY,
				ResultSet.CONCUR_READ_ONLY);
		ResultSet rs = ps
				.executeQuery("select clobid, mydata from myclob where clobid='2222' ");
		java.sql.Clob clob = null;
		if (rs.next()) {
			clob = rs.getClob("mydata");
			CLOB myclob = (CLOB) clob;
			long clen = myclob.length();
			char clobArray[] = new char[(int) clen];
			//Oracle extension. Return copy of the substring of the CLOB at the requested position.
		    //buffer - is the buffer into whcih the characters are read.
		    //pos - is the first character of the substring to be extracted.
		    //length - is the number of consecutive character to be copied.
			int readednum = myclob.getChars(1, (int) clen, clobArray);
			StringBuffer sb = new StringBuffer();
			sb.append(clobArray);
			str = sb.toString();
		}
		rs.close();
		ps.close();
		return str;
	}
}
